package org.abc.fund.service.factorService;

import org.abc.fund.entity.factorEntity.Factor;
import org.abc.fund.entity.factorEntity.NavData;
import org.abc.fund.repository.factorRepository.FactorRepository;
import org.abc.fund.repository.factorRepository.NavDataRepository;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.time.LocalDateTime;
import java.util.*;

@Service
public class FileParseService {
    @Autowired
    private FactorRepository factorRepository;
    /**
     * 解析文件用于预览（不保存到数据库）
     */
    public Map<String, Object> parseFileForPreview(MultipartFile file) {
        try {
            if (file.isEmpty()) {
                throw new IllegalArgumentException("文件为空");
            }

            String fileName = file.getOriginalFilename();
            if (fileName == null) throw new IllegalArgumentException("文件名为空");
            String lowerFileName = fileName.toLowerCase();
            if (!lowerFileName.endsWith(".csv") && !lowerFileName.endsWith(".xlsx") && !lowerFileName.endsWith(".xls")) {
                throw new IllegalArgumentException("文件类型不支持");
            }

            if (fileName.endsWith(".csv")) {
                return parseCSVForPreview(file);
            } else {
                return parseExcelForPreview(file);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 解析CSV文件用于预览
     */
    private Map<String, Object> parseCSVForPreview(MultipartFile file) {
        Map<String, Object> result = new HashMap<>();
        List<Map<String, Object>> dataList = new ArrayList<>();
        
        try (Reader reader = new InputStreamReader(file.getInputStream());
             CSVParser csvParser = CSVFormat.DEFAULT.builder().setHeader().setSkipHeaderRecord(true).build().parse(reader)) {

            // 获取列名
            Set<String> headers = csvParser.getHeaderMap().keySet();
            List<String> columnList = new ArrayList<>(headers);
            
            // 读取前10行数据用于预览
            int rowCount = 0;
            for (CSVRecord csvRecord : csvParser) {
                if (rowCount >= 10) break; // 只预览前10行
                
                Map<String, Object> row = new HashMap<>();
                for (String header : headers) {
                    String value = csvRecord.get(header);
                    // 尝试转换为数值
                    try {
                        if (value != null && !value.trim().isEmpty()) {
                            double numValue = Double.parseDouble(value);
                            row.put(header, numValue);
                        } else {
                            row.put(header, value);
                        }
                    } catch (NumberFormatException e) {
                        row.put(header, value);
                    }
                }
                dataList.add(row);
                rowCount++;
            }

            result.put("data", dataList);
            result.put("columns", columnList);
            result.put("totalRows", csvParser.getRecordNumber());
            result.put("previewRows", dataList.size());
            result.put("processedAt", LocalDateTime.now());
            
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 解析Excel文件用于预览
     */
    private Map<String, Object> parseExcelForPreview(MultipartFile file) {
        Map<String, Object> result = new HashMap<>();
        List<Map<String, Object>> dataList = new ArrayList<>();
        
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("文件没有表头");
            }

            // 获取列名
            List<String> headers = new ArrayList<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    headers.add(cell.toString().trim());
                } else {
                    headers.add("Column" + (i + 1));
                }
            }

            // 读取前10行数据用于预览
            int rowCount = 0;
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头
                if (rowCount >= 10) break; // 只预览前10行

                Map<String, Object> rowData = new HashMap<>();
                for (int i = 0; i < headers.size(); i++) {
                    Cell cell = row.getCell(i);
                    Object value = getCellValue(cell);
                    rowData.put(headers.get(i), value);
                }
                dataList.add(rowData);
                rowCount++;
            }

            result.put("data", dataList);
            result.put("columns", headers);
            result.put("totalRows", sheet.getLastRowNum());
            result.put("previewRows", dataList.size());
            result.put("processedAt", LocalDateTime.now());
            
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取单元格值
     */
    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    public List<Factor> parseAndSaveFile(MultipartFile file) {
        try {
            if (file.isEmpty()) {
                throw new IllegalArgumentException("文件为空");
            }

            String fileName = file.getOriginalFilename();
            if (fileName == null) throw new IllegalArgumentException("文件名为空");
            String lowerFileName = fileName.toLowerCase();
            if (!lowerFileName.endsWith(".csv") && !lowerFileName.endsWith(".xlsx") && !lowerFileName.endsWith(".xls")) {
                throw new IllegalArgumentException("文件类型不支持");
            }

            if (fileName.endsWith(".csv")) {
                return parseCSV(file);
            } else {
                return parseExcel(file);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private List<Factor> parseCSV(MultipartFile file) {
        List<Factor> factors = new ArrayList<>();
        String[] expectedHeaders = {"fundCode", "factorCode", "value", "factorName", "description", "factorType", "enabled"};
        try (Reader reader = new InputStreamReader(file.getInputStream());
             CSVParser csvParser = CSVFormat.DEFAULT.builder().setHeader(expectedHeaders).setSkipHeaderRecord(true).build().parse(reader)) {

            // 检查列名是否匹配
            for (String header : expectedHeaders) {
                if (!csvParser.getHeaderMap().containsKey(header)) {
                    throw new IllegalArgumentException("文件表头缺失或不匹配: " + header);
                }
            }

            int rowNum = 1;
            for (CSVRecord csvRecord : csvParser) {
                Factor factor = new Factor();
                factor.setFundCode(csvRecord.get("fundCode"));
                factor.setFactorCode(csvRecord.get("factorCode"));
                factor.setValue(csvRecord.get("value") != null ? Double.parseDouble(csvRecord.get("value")) : null);
                factor.setFactorName(csvRecord.get("factorName"));
                factor.setDescription(csvRecord.get("description"));
                factor.setFactorType(csvRecord.get("factorType"));
                factor.setEnabled(Boolean.parseBoolean(csvRecord.get("enabled")));

                // 验证数据类型
                if (!validateFactor(factor)) {
                    throw new IllegalArgumentException("第" + rowNum + "行数据不完整或格式错误");
                }
                factors.add(factor);
                rowNum++;
            }

            // 保存或更新数据
            saveOrUpdateFactors(factors);
            return factors;
        } catch (Exception e) {
            throw new RuntimeException("CSV文件解析失败: " + e.getMessage(), e);
        }
    }

    private List<Factor> parseExcel(MultipartFile file) {
        List<Factor> factors = new ArrayList<>();
        String[] expectedHeaders = {"fundCode", "factorCode", "value", "factorName", "description", "factorType", "enabled"};
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("文件没有表头");
            }

            // 创建列名到索引的映射
            Map<String, Integer> headerMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    String header = cell.toString().trim();
                    headerMap.put(header, i);
                }
            }

            // 检查列名是否匹配
            for (String expectedHeader : expectedHeaders) {
                if (!headerMap.containsKey(expectedHeader)) {
                    throw new IllegalArgumentException("文件表头缺失或不匹配: " + expectedHeader);
                }
            }

            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头

                Factor factor = new Factor();
                factor.setFundCode(row.getCell(headerMap.get("fundCode")).toString());
                factor.setFactorCode(row.getCell(headerMap.get("factorCode")).toString());
                factor.setValue(row.getCell(headerMap.get("value")).getCellType() == CellType.NUMERIC ? row.getCell(headerMap.get("value")).getNumericCellValue() : null);
                factor.setFactorName(row.getCell(headerMap.get("factorName")).toString());
                factor.setDescription(row.getCell(headerMap.get("description")).toString());
                factor.setFactorType(row.getCell(headerMap.get("factorType")).toString());
                factor.setEnabled(row.getCell(headerMap.get("enabled")).getCellType() == CellType.BOOLEAN ? row.getCell(headerMap.get("enabled")).getBooleanCellValue() : Boolean.parseBoolean(row.getCell(headerMap.get("enabled")).toString()));

                // 验证数据类型
                if (!validateFactor(factor)) {
                    throw new IllegalArgumentException("第" + (row.getRowNum() + 1) + "行数据不完整或格式错误");
                }
                factors.add(factor);
            }

            // 保存或更新数据
            saveOrUpdateFactors(factors);
            return factors;
        } catch (Exception e) {
            throw new RuntimeException("Excel文件解析失败: " + e.getMessage(), e);
        }
    }

    private void saveOrUpdateFactors(List<Factor> factors) {
        for (Factor factor : factors) {
            // 检查是否已存在相同的 factorCode
            Optional<Factor> existingFactorOpt = factorRepository.findByFactorCode(factor.getFactorCode());
            if (existingFactorOpt.isPresent()) {
                // 如果存在，更新现有记录
                Factor existingFactor = existingFactorOpt.get();
                existingFactor.setFundCode(factor.getFundCode());
                existingFactor.setFactorName(factor.getFactorName());
                existingFactor.setDescription(factor.getDescription());
                existingFactor.setFactorType(factor.getFactorType());
                existingFactor.setValue(factor.getValue());
                existingFactor.setEnabled(factor.getEnabled());
                factorRepository.save(existingFactor);
            } else {
                // 如果不存在，插入新记录
                factorRepository.save(factor);
            }

        }
    }

    private boolean validateFactor(Factor factor) {
        // 验证数据类型，根据实际需求修改
        if (factor.getFundCode() == null || factor.getFundCode().isEmpty()) {
            return false;
        }
        if (factor.getFactorCode() == null || factor.getFactorCode().isEmpty()) {
            return false;
        }
        if (String.valueOf(factor.getValue()) == null) {
            return false;
        }
        if (factor.getFactorName() == null || factor.getFactorName().isEmpty()) {
            return false;
        }
        if (factor.getDescription() == null || factor.getDescription().isEmpty()) {
            return false;
        }
        if (factor.getFactorType() == null || factor.getFactorType().isEmpty()) {
            return false;
        }
        if (factor.getEnabled() == null) {
            return false;
        }
        return true;
    }
    private double roundTo5(String value) {
        try {
            if (value == null) throw new IllegalArgumentException("空值");
            // 只保留第一个数字部分
            String cleaned = value.trim().split("\\s+")[0].replaceAll("[^\\d.\\-Ee]", "");
            return Math.round(Double.parseDouble(cleaned) * 1e5) / 1e5;
        } catch (Exception e) {
            throw new IllegalArgumentException("数值格式错误: " + value);
        }
    }
    private String getCellString(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case STRING:
                // 只取第一行，去除多余空白
                String str = cell.getStringCellValue();
                if (str.contains("\n")) {
                    str = str.split("\n")[0];
                }
                return str.trim();
            default:
                String str2 = cell.toString();
                if (str2.contains("\n")) {
                    str2 = str2.split("\n")[0];
                }
                return str2.trim();
        }
    }
    @Autowired
    private NavDataRepository navDataRepository;
    private List<NavData> saveNavDataFromExcel(MultipartFile file) {
        List<NavData> navDataList = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                if (row.getPhysicalNumberOfCells() != 4) {
                    throw new IllegalArgumentException("每行必须有且仅有4列，实际:" + row.getPhysicalNumberOfCells());
                }
                String col2 = getCellString(row.getCell(1));
                String col3 = getCellString(row.getCell(2));
                String col4 = getCellString(row.getCell(3));
                double value2 = roundTo5(col2);
                double value3 = roundTo5(col3);
                double value4 = roundTo5(col4);
                NavData navData = new NavData();
                navData.setAccumNav(value2);
                navData.setUnitNav(value3);
                navData.setDailyGrowthRate(value4);
                navDataList.add(navData);
            }
            navDataRepository.saveAll(navDataList);
            return navDataList;
        } catch (Exception e) {
            throw new RuntimeException("Excel文件解析失败: " + e.getMessage(), e);
        }
    }
    private List<NavData> saveNavDataFromCSV(MultipartFile file) {
        List<NavData> navDataList = new ArrayList<>();
        try (Reader reader = new InputStreamReader(file.getInputStream());
             CSVParser csvParser = CSVFormat.DEFAULT.builder().setSkipHeaderRecord(false).build().parse(reader)) {
            for (CSVRecord csvRecord : csvParser) {
                if (csvRecord.size() != 4) {
                    throw new IllegalArgumentException("每行必须有且仅有4列，实际:" + csvRecord.size());
                }
                // 只取前4列
                String col2 = csvRecord.get(1);
                String col3 = csvRecord.get(2);
                String col4 = csvRecord.get(3);
                // 保留5位小数
                double value2 = roundTo5(col2);
                double value3 = roundTo5(col3);
                double value4 = roundTo5(col4);
                NavData navData = new NavData();
                navData.setAccumNav(value2);
                navData.setUnitNav(value3);
                navData.setDailyGrowthRate(value4);
                navDataList.add(navData);
            }
            navDataRepository.saveAll(navDataList);
            return navDataList;
        } catch (Exception e) {
            throw new RuntimeException("CSV文件解析失败: " + e.getMessage(), e);
        }
    }
    public List<NavData> saveNavDataFromFile(MultipartFile file) {
        try {
            if (file.isEmpty()) {
                throw new IllegalArgumentException("文件为空");
            }
            String fileName = file.getOriginalFilename();
            if (fileName == null) throw new IllegalArgumentException("文件名为空");
            String lowerFileName = fileName.toLowerCase();
            if (!lowerFileName.endsWith(".csv") && !lowerFileName.endsWith(".xlsx") && !lowerFileName.endsWith(".xls")) {
                throw new IllegalArgumentException("文件类型不支持");
            }
            if (lowerFileName.endsWith(".csv")) {
                return saveNavDataFromCSV(file);
            } else {
                return saveNavDataFromExcel(file);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("保存基金原数据失败: " + e.getMessage(), e);
        }
    }
}
